package com.yumei.trader.gym.dbase.sqlite;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.yumei.trader.gym.object.ActObj;


public class SqliteActObject {

    private SQLiteDatabase db;

    private final DatabaseHelper dbHelper;

    private static SqliteActObject instance;

    public static SqliteActObject getInstance(Context context){

        if(instance == null){
            instance = new SqliteActObject(context);
        }
        return instance;
    }


    private SqliteActObject(Context context){

        dbHelper = new DatabaseHelper(context, Table.DATABASE_NAME, null,
                Table.DATABASE_VERSION); // 通过DatabaseHelper定义数据

        createTable();
    }


    public void createTable() {

        db = dbHelper.getWritableDatabase();

        String sql = "CREATE TABLE IF NOT EXISTS "+ Table.TABLE_ACCOUNT+
                " (ID INTEGER PRIMARY KEY, " +
                "userid VARCHAR, " +
                "account VARCHAR, " + //仿真帐户
                "totalrise FLOAT, " + //总收益率
                "totaltimes INTEGER, " + //总局次
                "updateTime VARCHAR," + //最后修改时间
                "isUpload VARCHAR," + //是否已上传
                "gameId VARCHAR," + //最后一局游戏id
                "profit, FlOAT," + //最后收益额
                "usetimes FLOAT)"; //可用次数
        try {
            db.execSQL(sql);
            Log.i("TAG", "数据库sql语句执行成功");
        } catch (SQLException ex) {// 异常处理
            Log.i("TAG", "数据库创建失败");
        }finally{
            close(db, null);
        }
    }


    public void dropTable(String tableName) {

        SQLiteDatabase db = dbHelper.getWritableDatabase();
        String sql = "DROP TABLE IF EXISTS " + tableName;
        try {
            db.execSQL(sql);
        } catch (SQLException ex) {// 异常处理
            ex.printStackTrace();
        }finally{
            close(db,null);
        }
    }




    public synchronized void insert(ActObj bean){

        db = dbHelper.getWritableDatabase();

        try{
            String sql = "insert into "+ Table.TABLE_ACCOUNT
                    + "(userid,account,totalrise,totaltimes,updateTime,isUpload,usetimes,gameId,profit) " +
                    "values(?,?,?,?,?,?,?,?,?)";

            String[] args = {bean.userId,
                            String.valueOf(bean.account),
                            String.valueOf(bean.totalRise),
                            String.valueOf(bean.totalTimes),
                            "0",
                            "1",
                            String.valueOf(bean.useTimes),
                            "null",
                            "0"};

            System.out.println("sql="+sql);

            db.execSQL(sql,args); // 执行指定 sql

            Log.i("TAG", "表信息插入成功");
        } catch (SQLException ex) {
            Log.i("TAG", "表信息插入异常");
        }finally{
            close(db,null);
        }
    }

    public synchronized ActObj getAccount(String userId) {

        return getItem("select * from "+Table.TABLE_ACCOUNT+" where userid=?",new String[]{userId});
    }

    public synchronized void setAccount(String userId, double account){

        String nowTimeStr = System.currentTimeMillis() + "";
        db = dbHelper.getWritableDatabase();
        try {
            ContentValues cv = new ContentValues();
            cv.put("account", String.valueOf(account));
            cv.put("updateTime", nowTimeStr);
            cv.put("isUpload", "1");
            String[] args = {userId};
            int num =  db.update(Table.TABLE_ACCOUNT, cv, "userid=?",args);
        }catch (Exception e){
            e.printStackTrace();
            Log.i("TAG", "表（帐户）信息更新异常");
        }finally {
            close(db, null);
        }
    }

    public synchronized void setProfit(String userId, int profit){

        String nowTimeStr = System.currentTimeMillis() + "";
        db = dbHelper.getWritableDatabase();
        try {
            ContentValues cv = new ContentValues();
            cv.put("profit", String.valueOf(profit));
            cv.put("updateTime", nowTimeStr);
            cv.put("isUpload", "1");
            String[] args = {userId};
            db.update(Table.TABLE_ACCOUNT, cv, "userid=?",args);
        }catch (Exception e){
            e.printStackTrace();
            Log.i("TAG", "表（帐户）信息更新异常");
        }finally {
            close(db, null);
        }
    }

    public synchronized boolean updateIsUpload(String userId, String uploadStatus){

        db = dbHelper.getWritableDatabase();
        try {
            ContentValues cv = new ContentValues();
            cv.put("isUpload", uploadStatus);
            cv.put("profit", "0");
            String[] args = {userId};
            int num =  db.update(Table.TABLE_ACCOUNT, cv, "userid=?",args);
            return (num > 0);
        }catch (Exception e){
            e.printStackTrace();
            Log.i("TAG", "表（帐户）信息更新异常");
        }finally {
            close(db, null);
        }
        return false;
    }

    public synchronized boolean updateGameId(String userId, String gameId){

        db = dbHelper.getWritableDatabase();
        try {
            ContentValues cv = new ContentValues();
            cv.put("gameId", gameId);
            String[] args = {userId};
            int num =  db.update(Table.TABLE_ACCOUNT, cv, "userid=?",args);
            return (num > 0);
        }catch (Exception e){
            e.printStackTrace();
            Log.i("TAG", "表（帐户）信息更新异常");
        }finally {
            close(db, null);
        }
        return false;
    }

    public synchronized boolean updateAccount(String userId, float value) {

        db = dbHelper.getWritableDatabase();
        Cursor c = null;
        try {
            String  sql = "update "+Table.TABLE_ACCOUNT+" set account=account+? "+
                    "where userid=?";

            String[] params = new String[]{
                    String.valueOf(value),
                    userId};
            db.execSQL(sql,params);

            return  true;
        } catch (Exception e) {
            e.printStackTrace();
            Log.i("TAG", "表信息更新异常");
        } finally {
            close(db, c);
        }
        return  false;
    }



    public synchronized boolean updateRise(String userId, float rise) {

        db = dbHelper.getWritableDatabase();
        Cursor c = null;
        try {
            String  sql = "update "+Table.TABLE_ACCOUNT+" set totalrise=totalrise+?,totaltimes=totaltimes+1 "+
                    "where userid=?";

            String[] params = new String[]{
                    String.valueOf(rise),
                    userId};
            db.execSQL(sql,params);

            return  true;
        } catch (Exception e) {
            e.printStackTrace();
            Log.i("TAG", "表信息更新异常");
        } finally {
            close(db, c);
        }
        return  false;
    }




    public synchronized boolean updateTimes(String userId, float times) {

        db = dbHelper.getWritableDatabase();
        Cursor c = null;
        try {
            String  sql = "update " + Table.TABLE_ACCOUNT + " set usetimes=usetimes+? "+
                        " where userid=?";
            System.out.println(sql);
            db.execSQL(sql,new String[]{String.valueOf(times),userId});

            return  true;
        } catch (Exception e) {
            e.printStackTrace();
            Log.i("TAG", "表信息更新异常");
        } finally {
            close(db, c);
        }
        return  false;
    }





    public boolean isExsit(String userId){

        boolean isExist = false;
        db = dbHelper.getWritableDatabase();

        Cursor c = db.rawQuery("select * from " + Table.TABLE_ACCOUNT +
                " where userid=?", new String[]{userId});
        try {
            while (c.moveToNext()) {
                isExist = true;
            }
        }catch(Exception e){
            e.printStackTrace();
            Log.i("TAG", "表（帐户）信息查询异常");
        }finally{
            close(db,c);
        }
        return isExist;
    }


    private synchronized ActObj getItem(String sql, String[] args){

        db = dbHelper.getWritableDatabase();
        Cursor c = db.rawQuery(sql, args);

        try{
            while (c.moveToNext()) {
                return  getItem(c);
            }

        }catch(Exception e){
            e.printStackTrace();
        }finally{

          close(db,c);
        }

        return new ActObj();
    }


    @SuppressLint("Range")
    private ActObj getItem(Cursor c){

        ActObj bean = new ActObj();

        bean.userId = c.getString(c.getColumnIndex("userid"));
        bean.account = c.getDouble(c.getColumnIndex("account"));
        bean.totalRise = c.getFloat(c.getColumnIndex("totalrise"));
        bean.totalTimes = c.getInt(c.getColumnIndex("totaltimes"));
        bean.useTimes = c.getFloat(c.getColumnIndex("usetimes"));
        bean.updateTime = c.getString(c.getColumnIndex("updateTime"));
        bean.isUpload = c.getString(c.getColumnIndex("isUpload"));
        bean.gameId = c.getString(c.getColumnIndex("gameId"));
        bean.profit = c.getInt(c.getColumnIndex("profit"));

        System.out.println(bean.toString());
        return bean;
    }



    private void close(SQLiteDatabase db,Cursor c){

        if(c !=null) {
            if (!c.isClosed()) {
                c.close();
            }
        }

        if(db !=null) {
            if (db.isOpen()) {
                db.close();
            }
        }
    }

}
